1. Technical Field
This present disclosure relates to a method, an apparatus, and a computer program product for optimizing parameterized models using a functional paradigm of spreadsheet software. More particularly, the present disclosure relates to computer methods and apparatuses for computing and optimizing responses of constrained differential and algebraic systems in a spreadsheet application.
2. Description of Related Art
Spreadsheet applications (such as Excel® of Microsoft Corp., hereafter “Excel”) are used universally for business as well as engineering and scientific data modeling and analysis. The widespread adoption of the spreadsheet application is supported in part by its inherent simplicity of defining formulas and manipulating data, in addition to a practical set of built-in functions, charting tools, and extensibility. Numerous extensions (aka Add-Ins) have been developed to extend Excel utility into various applications, including solutions to differential equations and optimization of algebraic and stochastic models. However, computational problems in constrained optimization involving systems of differential and differential—algebraic equations are beyond the limitations of existing Add-Ins. Such problems arise frequently in engineering and scientific applications when it is required to compute optimal values for a parameterized system of differential equations to satisfy objective constraints on the system response. Prior art Add-Ins do not offer an integrated solution to this class of problems in Excel spreadsheet application.
On the other hand, constrained optimization involving algebraic objective function and constraints has long been supported in Excel. In fact, Excel ships with a standard built in solver that is inherently limited to algebraic formulas that can be defined in Excel worksheets. Excel solver is an instance of an Excel command that is executed via an interactive dialogue, and performs operations in response to user actions as follows:                1. The user selects worksheet cells to hold initial values for each decision variable in a model.        2. The user creates a formula in another worksheet cell that calculates the objective function for the model.        3. The user creates additional formulas in worksheet cells to calculate the left hand sides of each constraint.        4. Via the command interface dialogue, the user selects the decision variables cells, the objective cell, the constraints cells, and enters desired bounds for each constraint and variable.        5. The solver iterates, altering the decision variables cells' values and recalculating the dependent objective and constraints cells values until such values are found which minimize the objective function value and satisfy the constraints bounds.        
When the solver completes, it will have overwritten the values of the input cells by the final results values.
A command is the standard mechanism utilized by an Add-In for evaluating formulas in Excel. Values for the independent cells are changed and the dependent formulas cells are recalculated by the spreadsheet application engine. Several available Add-Ins related to optimization and differential equations problems utilize the command approach to extend Excel utility. Frontline Systems Inc. offers through its website, Solver.com, a suite of solvers for optimization problems that are limited to algebraic and stochastic models. In contrast, solvers for differential equations in Excel have been rather limited. Polymath Software claims on their website, Polymath-Software.com, that their ODE_Solver Add-In to Excel is the only available solver for ordinary differential equations in Excel. Polymath ODE_Solver is described by Shacham et al in the paper: “Closing the Gap between Numerical Software Package and Spreadsheet Users in Process Computations” published in the proceedings ASEE Annual Conference, 2005 and is available online at Polymath-Software.com. ODE_Solver is based on the command approach, and is incapable of supporting constrained optimization on the differential equations. Joseph Billo, in his book titled: “Excel for Scientists and Engineers”, WILEY-INTERSCIENCE 2007, describes several command-based Visual Basic for Application (VBA) recipes to solve differential equations models in Excel. Similar to Polymath solver, these methods rely on the command approach for evaluating system formulas and are only suitable for simulating standalone, pre-configured differential equations, but are incapable of supporting constrained optimization of such systems.
An Excel command behavior differs fundamentally from a spreadsheet function because it mutates its inputs, and can trigger additional actions. Furthermore, as described in the article “Excel Commands, Functions, and States” which is available online from Microsoft MSDN library, commands cannot be invoked programmatically from spreadsheet functions, and they are never called by Excel during a recalculation. Commands are designed as interactive tools, and are not intended to be employed programmatically as re-usable functions in a tiered functional program. These restrictions limit the scope of problems that can be solved using the command mechanism.
On the other hand, the alternative mechanism for extending Excel's utility is through the addition of new spreadsheet functions. Excel permits only pure function behavior, and unlike commands, grants functions restricted access to its features through its Advanced Programming Interface (API). A pure function cannot mutate its input or any other value in Excel. It cannot evaluate formulas, and it is restricted to operating on constant inputs. Some external programs, such as MATLAB (distributed by MathWorks Inc.), offer interfaces to Excel to expose a portion of their functionality as extended Excel functions. Here, Excel primarily serves as a platform for inputting data through a proxy function while computations are performed utilizing the external program. This model permits exchange of basic data types such as numbers, but cannot be used to expose differential and optimization solvers, which require the ability to exchange and evaluate functions.
Whether utilizing the command or the function mechanisms for extending Excel spreadsheet utility, prior art has been confined to the inherent design limitations of either approach, and has been unable to extend the spreadsheet utility to compute optimal results for problems involving constrained optimization of parameterized differential equation systems.